﻿using FySystem.Data.OrmA.Db;
using FySystem.Data.OrmA.Formatter;
using FySystem.Data.OrmA.Reflection;
using FySystem.Data.OrmA.Resolvers.Lambdas;
using FySystem.IO;
using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace FySystem.Data.OrmA
{
    public class DBSession:IDisposable
    {
        internal DBHelper DbHelper;

        /// <summary>
        /// 数据库配置
        /// </summary>
        internal DbConfigs DBConfig;

        /// <summary>
        /// 创建DBSession
        /// </summary>
        /// <param name="dbConfig"></param>
        internal DBSession(DbConfigs dbConfig)
        {
            DBConfig = dbConfig;
        }

        /// <summary>
        /// 创建并打开连接
        /// </summary>
        internal void Open()
        {
            try
            {
                DbHelper = new DBHelper();
                DbHelper.CreateConnection(DBConfig);
                DbHelper.Open();
            }
            catch (Exception)
            {
                throw;
            }
        }

        #region 事务
        /// <summary>
        /// 创建事务
        /// </summary>
        internal void BeginTransaction()
        {
            try
            {
                DbHelper.BeginTransaction();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 提交事物
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                DbHelper.CommitTransaction();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 回滚事物
        /// </summary>
        public void RollbackTransaction()
        {
            try
            {
                DbHelper.RollbackTransaction();
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region SQL执行
        /// <summary>
        /// 获取首行首列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(StringBuilder sql, params object[] parameters)
        {
            try
            {
                return ExecuteScalar(sql.ToString(), parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 获取首行首列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params object[] parameters)
        {
            object res = null;
            try
            {
                FormatResult fmtSQL = BaseFormatter.CreateSQLAndParameters(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, sql, parameters);
                res = DbHelper.ExecuteScalar(DBConfig.DbProviderFactory, fmtSQL.Sql, fmtSQL.Parameters);
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }

        /// <summary>
        /// 根据表ID获取单行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="value"></param>
        /// <returns></returns>
        public T Get<T>(object value)
        {
            try
            {
                ReflectionInfo reflectionInfo = ReflectionManager.ReflectionModel<T>();                                                         //反射模型
                FormatResult fmtSQL = BaseFormatter.CreateGetSQL(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, reflectionInfo, value);     //创建SQL
                return DataCopy.CopyDataToSingle<T>(reflectionInfo, DbHelper, fmtSQL);                                                         //获取数据
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 根据ID返回单行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="value"></param>
        /// <returns></returns>
        public CSon GetC<T>(object value)
        {
            try
            {
                ReflectionInfo reflectionInfo = ReflectionManager.ReflectionModel<T>();                                                         //反射模型
                FormatResult fmtSQL = BaseFormatter.CreateGetSQL(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, reflectionInfo, value);     //创建SQL
                return DataCopy.CopyDataToSingleC(DbHelper, fmtSQL);                                                         //获取数据
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 数据模型保存
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model">实体数据</param>
        /// <param name="isReturn">返回的实体中是否带有主键，默认不带主键</param>
        /// <returns></returns>
        public T Save<T>(T model,bool isReturn = false)
        {
            try
            {
                ReflectionInfo reflectionInfo = ReflectionManager.ReflectionModel<T>();                                                         //反射模型
                model = DbProcess.SaveModel(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, reflectionInfo, DbHelper, model, isReturn);     //执行数据存储
            }
            catch (Exception)
            {
                throw;
            }
            return model;
        }

        /// <summary>
        /// 更新数据，主键不能为空
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        public int Update<T>(T model)
        {
            try
            {
                ReflectionInfo reflectionInfo = ReflectionManager.ReflectionModel<T>();
                FormatResult fmtSQL = BaseFormatter.CreateUpdateSQL(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, reflectionInfo, model);
                return DbHelper.ExecuteNonQuery(DBConfig.DbProviderFactory, fmtSQL.Sql, fmtSQL.Parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 根据主键删除实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete<T>(T model)
        {
            try
            {
                ReflectionInfo reflectionInfo = ReflectionManager.ReflectionModel<T>();
                FormatResult fmtSQL = BaseFormatter.CreateDeleteSQL(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, reflectionInfo, model);
                return DbHelper.ExecuteNonQuery(DBConfig.DbProviderFactory, fmtSQL.Sql, fmtSQL.Parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> Query<T>(string sql, params object[] parameters)
        {
            try
            {
                ReflectionInfo reflectionInfo = ReflectionManager.ReflectionModel<T>();
                FormatResult fmtSQL = BaseFormatter.CreateSQLAndParameters(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, sql, parameters);
                return DataCopy.CopyDataToList<T>(reflectionInfo, DbHelper, fmtSQL);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<CSon> QueryC(string sql,params object[] parameters)
        {
            try
            {
                FormatResult fmtSQL = BaseFormatter.CreateSQLAndParameters(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, sql, parameters);
                return DataCopy.CopyDataToListC(DbHelper, fmtSQL);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> Query<T>(StringBuilder sql,params object[] parameters)
        {
            try
            {
                return Query<T>(sql.ToString(), parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<CSon> QueryC(StringBuilder sql, params object[] parameters)
        {
            try
            {
                return QueryC(sql.ToString(), parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public T QuerySingle<T>(string sql,params object[] parameters)
        {
            try
            {
                ReflectionInfo reflectionInfo = ReflectionManager.ReflectionModel<T>();
                FormatResult fmtSQL = BaseFormatter.CreateSQLAndParameters(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, sql, parameters);
                fmtSQL.Sql = DBConfig.SQLFormatter.ToSingleSQL(fmtSQL.Sql);
                return DataCopy.CopyDataToSingle<T>(reflectionInfo, DbHelper, fmtSQL);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public CSon QuerySingleC(string sql, params object[] parameters)
        {
            try
            {
                FormatResult fmtSQL = BaseFormatter.CreateSQLAndParameters(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, sql, parameters);
                fmtSQL.Sql = DBConfig.SQLFormatter.ToSingleSQL(fmtSQL.Sql);
                return DataCopy.CopyDataToSingleC(DbHelper, fmtSQL);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public T QuerySingle<T>(StringBuilder sql,params object[] parameters)
        {
            try
            {
                return QuerySingle<T>(sql.ToString(), parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public CSon QuerySingleC(StringBuilder sql, params object[] parameters)
        {
            try
            {
                return QuerySingleC(sql.ToString(), parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql,params object[] parameters)
        {
            try
            {
                FormatResult fmtSQL = BaseFormatter.CreateSQLAndParameters(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, sql, parameters);
                return DbHelper.ExecuteNonQuery(DBConfig.DbProviderFactory, fmtSQL.Sql, fmtSQL.Parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(StringBuilder sql,params object[] parameters)
        {
            try
            {
                return ExecuteNonQuery(sql.ToString(), parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="pageNumber">当前页，从1开始</param>
        /// <param name="perPage">每页显示数量</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public PgResult<T> PageQuery<T>(string sql, int pageNumber, int perPage, string orderBy, params object[] parameters)
        {
            PgResult<T> res = new PgResult<T>()
            {
                PageNumber = pageNumber,
                PerPage = perPage
            };
            try
            {
                ReflectionInfo reflectionInfo = ReflectionManager.ReflectionModel<T>();
                PgFormatResult fmtSQL = BaseFormatter.CreatePageQuerySQL(DBConfig.SQLFormatter, DBConfig.DbProviderFactory, reflectionInfo, sql,
                    pageNumber, perPage, orderBy, parameters);

                //先查出总记录数量，并计算页数
                long count = long.Parse(DbHelper.ExecuteScalar(DBConfig.DbProviderFactory, fmtSQL.CountSql, fmtSQL.Parameters).ToString());
                res.TotalCount = count;
                res.TotalPage = (int)Math.Ceiling((double)count / perPage);

                //查询结果
                res.QueryResults = DataCopy.CopyDataToList<T>(reflectionInfo, DbHelper, fmtSQL);
            }
            catch (Exception)
            {
                throw;
            }
            return res;
        }
        #endregion

        #region 执行Lambda表达式
        /// <summary>
        /// 获取选择器（知识点欠缺，未能成功，下一版本再说）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public DbSelector<T> Select<T>()
        {
            return new DbSelector<T>(this);
        }

        /// <summary>
        /// 获取选择器（暂时只支持最简单的单表数据查询）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <returns></returns>
        public DbSelector<T> Select<T>(Expression<Func<T,object>> expression)
        {
            return new DbSelector<T>(this, expression);
        }
        #endregion

        #region 第二版的Lambda表达式，暂时想不出来怎么做

        #endregion

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            try
            {
                if (DbHelper != null)
                    DbHelper.Dispose();
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}
